package postjnlv2_param.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import postjnlv2_param.modele.ReadFile;




public class ArborCustD3Dao {
	
	private String userid = "arbor";
    private String password = "arbor123";
    private String pilote = "oracle.jdbc.driver.OracleDriver";
    private String url = "jdbc:oracle:thin:@10.1.3.133:1522:cc_cust";   

	private static Connection con;
	private ReadFile readFile;

     // constructor 
	public ArborCustD3Dao(){
	}

	public Connection getConnection(){
		readFile= new ReadFile();		
		try {
			Class.forName(pilote);	

		} catch(java.lang.ClassNotFoundException e) {
			System.err.print("ClassNotFoundException: ");
			System.err.println(e.getMessage());
		}

		try {
			con = DriverManager.getConnection(url, userid, password);
			System.out.println ("Ouverture Connection");     
		} catch(SQLException ex) {
			System.err.println("SQLException: " + ex.getMessage());
		}
		
		return con;
	}
	

	static  public  void  closeConnection(){         
		System.out.println ("Close DBConnection.");          
		        
				
		try {
			con.close();             
			System.out.println ("Fermeture Connection");        
			}         
		catch (Exception ex) {             
			System.out.println ("ERROR :"+ex.getMessage());             
			ex.printStackTrace();        
			}    
		} 
	
	
	//INFO MAX(JNL_END_DT)
	public String getMAX_JNL_END_DT_Dao(){	
			getConnection();	
			String date=null;
			try{
				String sql=readFile.getMAX_JNL_END_DT_Request();				
				// Create a prepared statement
	 			Statement s = con.createStatement();
				ResultSet rs = s.executeQuery(sql);	
				while(rs.next()){ 
					date = rs.getString("MAX(JNL_END_DT)");
				}
			}		
			catch (Exception e){
				System.out.println(e);
			}
			
			return date;
		}
		
		//INFO MAX(JNL_REF_NO)
		public ArrayList<String> getMAX_JNL_REF_NO_Dao(){
			getConnection();	
			String ref_no="";
			ArrayList<String> list_ref_no= new ArrayList<>();
			//lecture du fichier texte	
			try{
				String sql=readFile.getMAX_JNL_REF_NO_Request();				
				// Create a prepared statement
	 			Statement s = con.createStatement();
				ResultSet rs = s.executeQuery(sql);	
				while(rs.next()){ 
					ref_no = rs.getString("MAX(JNL_REF_NO)");
					list_ref_no.add(ref_no);
				}
			}		
			catch (Exception e){
				System.out.println(e);
			}
			
			return list_ref_no;
		}
		
		//INFO T_ID_RUN FROM CPTL_TRT01_POSTJNLV2_ARCH 
		public String getT_ID_RUN_Dao(String jnlRefNo1,String jnlRefNo2){
			getConnection();	
			String id_run="";
			//lecture du fichier texte	
			try{
				String sql=readFile.getT_ID_RUN_Request(jnlRefNo1,jnlRefNo2);				
				// Create a prepared statement
	 			Statement s = con.createStatement();
				ResultSet rs = s.executeQuery(sql);	
				while(rs.next()){ 
					id_run = rs.getString("T_ID_RUN");
				}
			}		
			catch (Exception e){
				System.out.println(e);
			}
			
			return id_run;
		}
		
		//UPDATE CPTL_TRT01_POSTJNLV2_ARCH
		public void getUpdate_CPTL_TRT01_POSTJNL_Dao(String idRun,String date1,String date2){
			getConnection();	
			try{
				String sql=readFile.getUpdate_CPTL_TRT01_POSTJNL_Request(idRun,date1,date2);
				String delimiter = ";";
				String[] requetes = sql.split(delimiter);
				System.out.println("REQUETES UPDATE CPTL_TRT01_POSTJNL : "+sql);
				for(int i=0;i<requetes.length-1;i++){
					// Create a prepared statement
					System.out.println("je suis dans la boucle");
					String req=requetes[i];
					//prepare statement
					PreparedStatement ps = con.prepareStatement(req);
					System.out.println("j'ai ma requete");
					System.out.println(req);
					ps.executeUpdate();
					System.out.println("je l'execute");
				}								
			}		
			catch (Exception e){
				System.out.println(e);
			}			
		}
		
		//UPDATE JNL_RUNS_STATUS
		public void getUpdate_JNL_RUNS_STATUS_Dao(String date1,String jnlRefNo1,String jnlRefNo2){
			getConnection();	
			try{
				String sql=readFile.getUpdate_JNL_RUNS_STATUS_Request(date1,jnlRefNo1,jnlRefNo2);
				String delimiter = ";";
				String[] requetes = sql.split(delimiter);
				System.out.println("REQUETES UPDATE JNL_RUNS_STATUS : "+sql);
				for(int i=0;i<requetes.length-1;i++){
					// Create a prepared statement
					System.out.println("je suis dans la boucle");
					String req=requetes[i];
					//prepare statement
					PreparedStatement ps = con.prepareStatement(req);
					System.out.println("j'ai ma requete");
					System.out.println(req);
					ps.executeUpdate();
					System.out.println("je l'execute");
				}				
			}		
			catch (Exception e){
				System.out.println(e);
			}
			
		}
		
		//MISE A JOUR EN DUR DE LA PROC STOCK Cptl_Trt01_Postjnlv2_Pkg
		public void getReplace_Cptl_Trt01_Postjnlv2_Pkg_Dao(String idRun){
			getConnection();	
			try{
				String sql=readFile.getReplace_Cptl_Trt01_Postjnlv2_Pkg_Request(idRun);
				//prepare statement
				PreparedStatement ps = con.prepareStatement(sql);
				ps.executeUpdate();
			}		
			catch (Exception e){
				System.out.println(e);
			}
			
		}
		
		//Commit info
		public void getCommit_Dao(){
			//lecture du fichier texte	
			try{
				System.out.println("AVANT COMMIT");
				con.commit();
				System.out.println("APRES COMMIT");
			}		
			catch (Exception e){
				System.out.println(e);
			}
			
		}
		
		
		//Commit rollback
		public void getRollBack_Dao(){
			//lecture du fichier texte	
			try{
				
				con.rollback();
			}		
			catch (Exception e){
				System.out.println(e);
			}
			
		}
}
	
